﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data;
using System.IO;

namespace SocialIn.Core.Data
{
    /// <summary>
    /// ADO.NET 작업 Helper
    /// </summary>
    public sealed class SqlHelper
    {
        #region Private Members & constructors
        private string _DbProvider = null;
        private string _ConnectionString = null;

        /// <summary>
        /// DB Connection String<br/>
        /// Connection String
        /// </summary>
        /// <param name="dbProvider">DB Provider string</param>
        /// <param name="connectionString">DB Connection String</param>
        public SqlHelper(string dbProvider, string connectionString)
        {
            this._DbProvider = dbProvider;
            this._ConnectionString = connectionString;
        }
        #endregion

        #region ExecuteNonQuery
        /// <summary>
        /// 단일 쿼리 실행(Insert, Update, Delete)
        /// </summary>
        /// <param name="query">쿼리</param>
        /// <returns>트랜잭션 성공 여부</returns>
        public Result<int> ExecuteNonQuery(string query)
        {
            return ExecuteNonQuery(query, null);
        }

        public Result<int> ExecuteNonQuery(string query, Dictionary<string, string> parameter)
        {
            List<string> queries = new List<string>(1);
            queries.Add(query);

            List<Dictionary<string, string>> parameters = null;

            if (parameter != null)
            {
                parameters = new List<Dictionary<string, string>>(1);
                parameters.Add(parameter);
            }

            return ExecuteNonQuery(queries, parameters);
        }

        public Result<int> ExecuteNonQuery(List<string> queries, List<Dictionary<string, string>> parameters)
        {
            Result<int> result = new Result<int>();

            DbProviderFactory dbFactory = DbProviderFactories.GetFactory(this._DbProvider);

            using (DbConnection dbCon = dbFactory.CreateConnection())
            {
                dbCon.ConnectionString = this._ConnectionString;

                DbCommand dbCmd = dbFactory.CreateCommand();
                dbCmd.Connection = dbCon;
                dbCmd.CommandType = CommandType.Text;

                dbCon.Open();

                DbTransaction dbTx = dbCon.BeginTransaction();
                dbCmd.Transaction = dbTx;

                try
                {
                    #region ExecuteNonQuery

                    int totalEffectRows = 0;

                    for (int i = 0; i < queries.Count; i++)
                    {
                        dbCmd.CommandText = queries[i];

                        if (parameters != null)
                        {
                            if (parameters[i] != null)
                            {
                                foreach (KeyValuePair<string, string> pair in parameters[i])
                                {
                                    DbParameter param = dbFactory.CreateParameter();
                                    param.ParameterName = pair.Key;
                                    param.Value = pair.Value;

                                    dbCmd.Parameters.Add(param);
                                }
                            }
                        }

                        totalEffectRows += dbCmd.ExecuteNonQuery();
                    }
                    #endregion

                    dbTx.Commit();

                    result.Completed = true;
                    result.ReturnValue = totalEffectRows;
                }
                catch (Exception ex)
                {
                    dbTx.Rollback();

                    result.Completed = false;
                    result.Error = ex;
                    result.Message = ex.Message;
                    result.ReturnValue = -1;
                }
                finally
                {
                    if (dbCmd != null)
                        dbCmd.Dispose();

                    if (dbTx != null)
                        dbTx.Dispose();
                }
            }

            return result;
        }
        #endregion

        #region ExecuteDataSet
        /// <summary>
        /// 단일 검색 된 결과를 DataSet에 넣습니다.
        /// </summary>
        /// <param name="query">검색 쿼리</param>
        /// <returns>검색 결과</returns>
        public Result<DataSet> ExecuteDataSet(string query)
        {
            return this.ExecuteDataSet(query, null);
        }

        public Result<DataSet> ExecuteDataSet(string query, Dictionary<string, string> parameter)
        {
            List<string> queries = new List<string>(1);
            queries.Add(query);

            List<Dictionary<string, string>> parameters = null;

            if (parameter != null)
            {
                parameters = new List<Dictionary<string, string>>(1);
                parameters.Add(parameter);
            }

            return this.ExecuteDataSet(queries, parameters);
        }
        /// <summary>
        /// 다중 검색 된 결과를 DataSet에 넣습니다.
        /// </summary>
        /// <param name="queries">검색 쿼리 리스트</param>
        /// <returns>검색 결과</returns>
        public Result<DataSet> ExecuteDataSet(List<string> queries, List<Dictionary<string, string>> parameters)
        {
            Result<DataSet> result = new Result<DataSet>();

            string errorQuery = "";

            try
            {
                DataSet ds = new DataSet();

                DbProviderFactory dbFactory = DbProviderFactories.GetFactory(this._DbProvider);

                using (DbConnection dbCon = dbFactory.CreateConnection())
                {
                    dbCon.ConnectionString = this._ConnectionString;

                    using (DbDataAdapter dbAapter = dbFactory.CreateDataAdapter())
                    {
                        using (DbCommand dbCmd = dbFactory.CreateCommand())
                        {
                            dbCmd.Connection = dbCon;
                            dbCmd.CommandType = CommandType.Text;

                            #region Aapter Fill
                            for (int i = 0; i < queries.Count; i++)
                            {
                                dbCmd.CommandText = queries[i];

                                if (parameters != null)
                                {
                                    if (parameters[i] != null)
                                    {
                                        foreach (KeyValuePair<string, string> pair in parameters[i])
                                        {
                                            DbParameter param = dbFactory.CreateParameter();
                                            param.ParameterName = pair.Key;
                                            param.Value = pair.Value;

                                            dbCmd.Parameters.Add(param);
                                        }
                                    }
                                }

                                dbAapter.SelectCommand = dbCmd;

                                errorQuery = dbCmd.CommandText;

                                DataTable table = new DataTable();
                                dbAapter.Fill(table);

                                ds.Tables.Add(table);
                            }
                            #endregion
                        }
                    }
                }

                result.Completed = true;
                result.ReturnValue = ds;
            }
            catch (Exception ex)
            {
                result.Completed = false;
                result.Error = ex;
                result.Message = ex.Message;
                result.ReturnValue = null;
            }

            return result;
        }
        #endregion

        #region ExecuteDataTable
        /// <summary>
        /// 단일 검색 된 결과를 DataSet에 넣습니다.
        /// </summary>
        /// <param name="query">검색 쿼리</param>
        /// <returns>검색 결과</returns>
        public Result<DataTable> ExecuteDataTable(string query)
        {
            return this.ExecuteDataTable(query, null);
        }

        public Result<DataTable> ExecuteDataTable(string query, Dictionary<string, string> parameter)
        {
            List<string> queries = new List<string>(1);
            queries.Add(query);

            List<Dictionary<string, string>> parameters = new List<Dictionary<string, string>>(1);
            parameters.Add(parameter);

            Result<DataSet> temp = ExecuteDataSet(queries, parameters);

            Result<DataTable> result = new Result<DataTable>();
            result.Completed = temp.Completed;
            result.Error = temp.Error;
            result.Message = temp.Message;

            if (temp.ReturnValue != null)
                result.ReturnValue = temp.ReturnValue.Tables[0];

            return result;
        }
        #endregion

        #region ExecuteReader
        /// <summary>
        /// 단일 검색 결과를 DataReaer로 반환
        /// </summary>
        /// <param name="query">검색 쿼리</param>
        /// <returns>검색된 DataReader</returns>
        public Result<DbDataReader> ExecuteReader(string query)
        {
            return this.ExecuteReader(query, null);   
        }

        public Result<DbDataReader> ExecuteReader(string query, Dictionary<string, string> parameter)
        {
            Result<DbDataReader> result = new Result<DbDataReader>();

            try
            {
                DbProviderFactory dbFactory = DbProviderFactories.GetFactory(this._DbProvider);

                DbConnection dbCon = dbFactory.CreateConnection();
                dbCon.ConnectionString = this._ConnectionString;

                DbCommand dbCmd = dbFactory.CreateCommand();
                dbCmd.Connection = dbCon;
                dbCmd.CommandType = CommandType.Text;

                dbCon.Open();

                dbCmd.CommandText = query;

                if (parameter != null)
                {
                    foreach (KeyValuePair<string, string> pair in parameter)
                    {
                        DbParameter param = dbFactory.CreateParameter();
                        param.ParameterName = pair.Key;
                        param.Value = pair.Value;

                        dbCmd.Parameters.Add(param);
                    }
                }

                result.Completed = true;
                result.ReturnValue = dbCmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                result.Completed = false;
                result.Error = ex;
                result.Message = ex.Message;
                result.ReturnValue = null;
            }
            
            return result;
        }
        #endregion

        #region ExecuteScalar
        /// <summary>
        /// 첫번째 열의 첫 컬럼만 반환
        /// </summary>
        /// <param name="query">검색쿼리</param>
        /// <returns>검색된 Object</returns>
        public Result<object> ExecuteScalar(string query)
        {
            return this.ExecuteScalar(query, null);
        }

        public Result<object> ExecuteScalar(string query, Dictionary<string, string> parameter)
        {
            Result<object> result = new Result<object>();

            try
            {
                DbProviderFactory dbFactory = DbProviderFactories.GetFactory(this._DbProvider);

                using (DbConnection dbCon = dbFactory.CreateConnection())
                {
                    dbCon.ConnectionString = this._ConnectionString;

                    using (DbCommand dbCmd = dbFactory.CreateCommand())
                    {
                        dbCmd.Connection = dbCon;
                        dbCmd.CommandType = CommandType.Text;
                        dbCmd.CommandTimeout = 1000;

                        dbCon.Open();

                        dbCmd.CommandText = query;

                        if (parameter != null)
                        {
                            foreach (KeyValuePair<string, string> pair in parameter)
                            {
                                DbParameter param = dbFactory.CreateParameter();
                                param.ParameterName = pair.Key;
                                param.Value = pair.Value;

                                dbCmd.Parameters.Add(param);
                            }
                        }

                        result.Completed = true;
                        result.ReturnValue = dbCmd.ExecuteScalar();
                    }
                }
            }
            catch (Exception ex)
            {
                result.Completed = false;
                result.Error = ex;
                result.Message = ex.Message;
                result.ReturnValue = null;
            }

            return result;
        }
        #endregion
    }
}
